﻿if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_ot_pay')
          and type in ('P','PC'))
   drop procedure bd_proc_ot_pay
go


create procedure bd_proc_ot_pay (@ids varchar(max),@settleType int,@settleValueOrdinary decimal(18,2),@saveValueOrdinary decimal(18,2),@settleValueWeekend decimal(18,2),@saveValueWeekend decimal(18,2),@prevSettleValueOrdinary decimal(18,2),@prevSaveValueOrdinary decimal(18,2),@prevSettleValueWeekend decimal(18,2),@prevSaveValueWeekend decimal(18,2),@operator varchar(50)) as
BEGIN
	--计算加班支付
	--思路：根据ids 得到要加班支付的记录，之后分别做计算当月，计算上月，计算结余逻辑操作，并更新bd_kq_ot_serttle表，
				--最后将最终结果插入或更新时间银行表bd_kq_holidays_time

	--逻辑：
				--计算当月：根据值或比例将本月的平时加班剩余和双休加班剩余分配给支付工作和存工；
				--计算上月：根据值或比例将本月的平时加班剩余和双休加班剩余分配给支付工作和存工，若@isHoliday 是0 ,即不分配，则默认全部分配给存工
				--计算结余；将当月平时加班和上月剩余平时加班存工相加，本月双休加班和上月剩余双休加班相加，最终欠工 = 上月欠工+本月欠工。

	--参数


	--临时表
	create table #settle(
		id bigint not null
	)

	--创建 返回不满足请假的人的原因
	create table #return_table(
		user_serial numeric not null ,
		user_no VARCHAR(64) COLLATE Chinese_PRC_CI_AS not NULL,
		user_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS not NULL,
		dept_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS not NULL,
		error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS not NULL,
		id bigint null
	)

	begin try
		begin tran

			--1、将要计算支付的记录id 存入#serttle
			insert into #settle
			select * from string_split(@ids,',')

			--过滤掉不合格的（放到#return_talbe中，返回）
			insert into #return_table
			select
				u.user_serial,
				u.user_no,
				u.user_lname,
				d.dep_name,
				'考勤员未封帐，不能重新支付结算',
				se.id
				from #settle se
				INNER JOIN bd_kq_ot_settle s on s.id = se.id
				INNER JOIN dt_user u on s.user_serial = u.user_serial
				INNER JOIN dt_dep d on u.user_dep = d.dep_serial
				left JOIN bd_kq_accounts a on u.user_serial = a.user_serial and s.settle_year = a.year and s.settle_month = a.month
				where isnull(a.lock_status,0)<1
			--从#settle中移除不满足条件的人的单子id
			delete se from #settle se INNER JOIN #return_table r on r.id = se.id

			insert into #return_table
			select
				u.user_serial,
				u.user_no,
				u.user_lname,
				d.dep_name,
				'HR已封帐，不能重新支付结算',
				se.id
				from #settle se
				INNER JOIN bd_kq_ot_settle s on s.id = se.id
				INNER JOIN dt_user u on s.user_serial = u.user_serial
				INNER JOIN dt_dep d on u.user_dep = d.dep_serial
				left JOIN bd_kq_accounts a on u.user_serial = a.user_serial and s.settle_year = a.year and s.settle_month = a.month
				where isnull(a.lock_status,0)>1

			--从#settle中移除不满足条件的人的单子id
			delete se from #settle se INNER JOIN #return_table r on r.id = se.id

			--2、计算当月
			update s
				set cur_month_settle_ordinary = case @settleType when 0 then @settleValueOrdinary*60.0
							else cur_month_deduction_ordinary * @settleValueOrdinary/100.0 end,
						cur_month_settle_weekend = case @settleType when 0 then @settleValueWeekend*60.0
							else cur_month_deduction_weekend * @settleValueWeekend/100.0 end,
						cur_month_save_ordinary =case @settleType when 0 then @saveValueOrdinary*60.0
							else cur_month_deduction_ordinary * @saveValueOrdinary/100.0 end ,
						cur_month_save_weekend = case @settleType when 0 then @saveValueWeekend*60.0
							else cur_month_deduction_weekend * @saveValueWeekend/100.0 end
			from bd_kq_ot_settle s
			inner join #settle e on s.id = e.id

			--3、计算上月
			update s
				set prev_month_settle_ordinary = case @settleType when 0 then @prevSettleValueOrdinary*60.0
							else prev_month_deduction_ordinary * @prevSettleValueOrdinary/100.0 end,
						prev_month_settle_weekend = case @settleType when 0 then @prevSettleValueWeekend*60.0
							else prev_month_deduction_weekend * @prevSettleValueWeekend/100.0 end,
						prev_month_save_ordinary =case @settleType when 0 then @prevSaveValueOrdinary*60.0
							else prev_month_deduction_ordinary * @prevSaveValueOrdinary/100.0 end ,
						prev_month_save_weekend = case @settleType when 0 then @prevSaveValueWeekend*60.0
							else prev_month_deduction_weekend * @prevSaveValueWeekend/100.0 end
			from bd_kq_ot_settle s
			inner join #settle e on s.id = e.id

			--4、计算结余
	-- 		cur_month_result_ordinary
	-- 		cur_month_result_weekend
	-- 		prev_month_result_owe
	--
			update s
				set cur_month_result_ordinary = cur_month_save_ordinary + prev_month_save_ordinary,
						cur_month_result_weekend = cur_month_save_weekend + prev_month_save_weekend ,
						prev_month_result_owe = cur_month_owe
			from bd_kq_ot_settle s
			inner join #settle e on s.id = e.id

			--5、新增或更新时间银行


			--更新
			update t
				set save_ordinary = s.cur_month_result_ordinary,
						save_weekend = s.cur_month_result_weekend,
						owe = s.prev_month_result_owe,
						rest_total = s.cur_month_result_ordinary + s.cur_month_result_weekend,
						operate_id = @operator,
						operate_time = getdate()
			from bd_kq_holidays_time t
			INNER JOIN bd_kq_ot_settle s on t.user_serial = s.user_serial and t.time_year = s.settle_year
			and t.time_month = s.settle_month
			INNER JOIN #settle e on s.id = e.id

			--插入
			insert into bd_kq_holidays_time(
				user_serial,
				user_dept,
				time_year,
				time_month,
				save_ordinary,
				save_weekend,
				owe,
				vacation_annual,
				vacation_lactational,
				rest_total,
				operate_id,
				operate_time
			)
			select
						c.user_serial,
						c.user_dept,
						c.settle_year,
						c.settle_month,
						c.cur_month_result_ordinary,
						c.cur_month_result_weekend,
						c.prev_month_result_owe,
						isnull(t.vacation_annual,0),
						isnull(t.vacation_lactational,0),
						c.cur_month_result_ordinary + c.cur_month_result_weekend,
						@operator,
						getdate()
			from  (
						select s.*,convert(varchar(7),
									convert(date, concat(s.settle_year,'-',s.settle_month,'-01'),120)) as yearAndmonth
							from  bd_kq_ot_settle s
							left JOIN bd_kq_holidays_time t on t.user_serial = s.user_serial and t.time_year = s.settle_year
							and t.time_month = s.settle_month
							INNER JOIN #settle e on s.id = e.id
							where t.id is null
					) c
			LEFT JOIN bd_kq_holidays_time t on t.user_serial = c.user_serial
				and t.time_year = DATEPART(year, DATEADD(month, -1,  c.yearAndMonth+'-01'))
				and t.time_month = DATEPART(month, DATEADD(month, -1,  c.yearAndMonth+'-01'))

			--返回错误数据
				select * from #return_table
			commit tran
		end try
	begin catch
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			END
			RAISERROR (@errStr, 16, 1);
	end catch
END
go
